﻿Public Class frm_Asignacion_de_Turnos

    Dim acceso As New acceso_a_datos
    Dim carga_combo As New combo
    Dim busco As Boolean
    Dim primera_busqueda As Boolean = False
    Dim cargo As Boolean = False

    Private Sub frm_Asignacion_de_Turnos_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        cargar_combo(sender, e)
        Me.lbl_nueva.Visible = True
        Me.lbl_busq.Visible = False
        carga_grilla()
    End Sub

    Private Sub btn_buscar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_buscar.Click
        txt_apellido.Enabled = True
        txt_numero_doc.Enabled = True
        txt_apellido.Focus()
        Me.lbl_nueva.Visible = False
        Me.lbl_busq.Visible = True


        If txt_apellido.Text = "" And txt_numero_doc.Text = "" And txt_numero_doc.Enabled = True And primera_busqueda = True Then
            MsgBox("Primero debe Ingresar un Criterio de Busqueda ", vbExclamation, "Atencion")
        End If

        If Me.primera_busqueda = False Then
            limpiar()
        End If

        Me.primera_busqueda = True

        If Me.txt_apellido.Text <> "" And Me.txt_numero_doc.Text <> "" Then
            Me.buscar_por_nroDoc_apellido()
            primera_busqueda = False
            Me.lbl_busq.Visible = False
            Me.lbl_nueva.Visible = True
            busco = True
            Exit Sub
        End If

        If Me.txt_apellido.Text = "" And Me.txt_numero_doc.Text <> "" Then
            buscar_por_nroDoc(txt_numero_doc.Text)
            Me.primera_busqueda = False
            Me.lbl_busq.Visible = False
            Me.lbl_nueva.Visible = True
            busco = True
            Exit Sub
        End If

        If Me.txt_apellido.Text <> "" And Me.txt_numero_doc.Text = "" Then
            Me.buscar_por_apellido()
            Me.primera_busqueda = False
            Me.lbl_busq.Visible = False
            Me.lbl_nueva.Visible = True
            busco = True
            Exit Sub
        End If

    End Sub

    Private Sub btn_agregar_turno_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_agregar_turno.Click
        Turno.ShowDialog()
        cargar_combo(sender, e)
    End Sub

    Private Sub btn_asignar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_asignar.Click
        If txt_descipcion.Text <> "" And busco = True Then
            asignar()
            limpiar()
            carga_grilla()
        End If
    End Sub

    Private Sub btn_desasignar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_desasignar.Click
        If cargo = True Then
            desasignar()
        Else
            MsgBox("Primero seleccione un empleado", vbExclamation, "Importante")
        End If
        carga_grilla()
    End Sub

    Private Sub btn_nuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        btn_asignar.Enabled = False
        btn_desasignar.Enabled = False
        limpiar()
    End Sub

    Private Sub btn_salir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_salir.Click
        Close()
    End Sub

    Private Sub txt_apellido_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_apellido.KeyPress
        Select Case Asc(e.KeyChar)
            Case 4, 24, 4, 19, 127, 13, 9, 15, 14, 8
                Exit Sub
        End Select

        If IsNumeric(e.KeyChar) Then
            MsgBox("Solamente Ingrese Letras", MsgBoxStyle.Information, "Atención")
            e.KeyChar = ""
        End If
    End Sub

    Private Sub txt_numero_doc_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_numero_doc.KeyPress
        Select Case Asc(e.KeyChar)
            Case 4, 24, 4, 19, 127, 13, 9, 15, 14, 8
                Exit Sub
        End Select

        If Not IsNumeric(e.KeyChar) Then
            MsgBox("Solamente Ingrese Números", MsgBoxStyle.Information, "Atención")
            e.KeyChar = ""
        End If
    End Sub

    Public Sub cargar_combo(ByVal sender As Object, ByVal e As System.EventArgs)
        carga_combo.cargar(cmb_turno, "Turno", "nombre", "cod_Turno")
        cmb_turno_SelectionChangeCommitted(sender, e)
    End Sub

    'Carga los campos fecha desde y fecha hasta, cada ves que cambia la seleccion del combo
    Private Sub cmb_turno_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_turno.SelectionChangeCommitted
        acceso._tabla = "Turno"


        Dim id_turno As String = cmb_turno.SelectedValue.ToString
        Dim tabla = acceso.leo_tabla("cod_Turno = " + id_turno)


        txt_hora_desde.Text = FormatDateTime(tabla.Rows(0)("hora_Desde"), DateFormat.ShortTime)
        txt_hora_hasta.Text = FormatDateTime(tabla.Rows(0)("hora_Hasta"), DateFormat.ShortTime)
    End Sub

    'LIMPIA LOS CAMPOS
    Public Sub limpiar()
        txt_apellido.Text = ""
        txt_nombre.Text = ""
        txt_numero_doc.Text = ""
        txt_descipcion.Text = ""
        cmb_turno.SelectedValue = 1
        'txt_apellido.Enabled = False
        'txt_numero_doc.Enabled = False
        btn_asignar.Enabled = False
        btn_desasignar.Enabled = False
        primera_busqueda = False
        Me.txt_apellido.Enabled = True
        Me.txt_numero_doc.Enabled = True
        Me.dtp_fecha_desde.Enabled = True
        Me.cmb_turno.Enabled = True
        Me.txt_descipcion.Enabled = True
        Me.btn_agregar_turno.Enabled = True
        busco = False
        cargo = False

    End Sub

    'CARGAMOS LA GRILLA
    Private Sub carga_grilla()

        Dim tabla As New Data.DataTable
        Dim txt_sql As String = ""
        txt_sql = "SELECT EmpleadoxTurno.descripcion, EmpleadoxTurno.fecha_desde, Turno.nombre AS n_turno, Turno.cod_Turno, Empleado.nombre AS n_empleado, Empleado.apellido, Empleado.numero_Doc "
        txt_sql += " FROM EmpleadoxTurno INNER JOIN "
        txt_sql += " Empleado ON EmpleadoxTurno.tipo_Doc = Empleado.tipo_Doc AND EmpleadoxTurno.numero_Doc = Empleado.numero_Doc INNER JOIN"
        txt_sql += " Turno ON EmpleadoxTurno.cod_Turno = Turno.cod_Turno"
        txt_sql += " WHERE (EmpleadoxTurno.fecha_hasta IS NULL)"


        tabla = acceso._consulta(txt_sql)

        grilla_turnos_empleado.Rows.Clear()

        'cargamos la grilla con un metodo
        Dim c As Integer = 0

        For c = 0 To tabla.Rows.Count - 1
            grilla_turnos_empleado.Rows.Add(tabla.Rows(c)("n_empleado"), tabla.Rows(c)("apellido"), tabla.Rows(c)("n_turno"), tabla.Rows(c)("descripcion"), FormatDateTime(tabla.Rows(c)("fecha_desde"), DateFormat.ShortDate), tabla.Rows(c)("numero_Doc"), tabla.Rows(c)("cod_Turno"))
        Next
    End Sub

    'DOBLE CLICK GRILLA
    Private Sub grilla_turnos_empleado_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grilla_turnos_empleado.CellDoubleClick
        btn_desasignar.Enabled = True
        Dim nroDoc As Integer = grilla_turnos_empleado.CurrentRow.Cells("doc").Value
        Dim idturno As Integer = grilla_turnos_empleado.CurrentRow.Cells("idturno").Value
        txt_numero_doc.Text = nroDoc
        buscar_por_nroDoc(nroDoc)

        cmb_turno.SelectedValue = idturno
        cmb_turno_SelectionChangeCommitted(sender, e)
        txt_nombre.Text = grilla_turnos_empleado.CurrentRow.Cells("nombre").Value
        txt_apellido.Text = grilla_turnos_empleado.CurrentRow.Cells("apellido").Value
        txt_descipcion.Text = grilla_turnos_empleado.CurrentRow.Cells("descrip").Value
        cargo = True
        Me.txt_apellido.Enabled = False
        Me.txt_numero_doc.Enabled = False
        Me.dtp_fecha_desde.Enabled = False
        Me.cmb_turno.Enabled = False
        Me.txt_descipcion.Enabled = False
        Me.btn_agregar_turno.Enabled = False

    End Sub

    Private Sub buscar_por_nroDoc_apellido()
        Dim cons As String = ""
        acceso._tabla = "Empleado"

        Dim tabla As New Data.DataTable

        If Me.txt_apellido.Text <> "" And Me.txt_numero_doc.Text <> "" Then
            cons = "SELECT * FROM Empleado WHERE numero_Doc =" & Me.txt_numero_doc.Text
            cons &= " and apellido LIKE '" & Me.txt_apellido.Text & "%'"

            tabla = acceso._consulta(cons)


            If tabla.Rows.Count <> 0 Then
                txt_apellido.Text = tabla.Rows(0)("apellido")
                Me.txt_nombre.Text = tabla.Rows(0)("nombre")
                Me.txt_numero_doc.Text = tabla.Rows(0)("numero_Doc")
                Me.txt_apellido.Focus()
                Exit Sub
            Else
                MsgBox("No se encontro ningun Empleado con el Numero de Documento o Apellido especificado", vbExclamation, "Importante")
                Me.txt_numero_doc.Text = ""
                Me.txt_apellido.Text = ""
                Me.txt_apellido.Focus()
            End If
        End If
    End Sub

    Private Sub buscar_por_nroDoc(ByVal nroDoc As Integer)

        Dim cons As String = ""
        acceso._tabla = "Empleado"

        Dim tabla As New Data.DataTable

        If Me.txt_numero_doc.Text <> "" And Me.txt_apellido.Text = "" Then
            cons = "SELECT * FROM Empleado WHERE numero_DOc =" & nroDoc

            tabla = acceso._consulta(cons)

            If tabla.Rows.Count <> 0 Then

                txt_apellido.Text = tabla.Rows(0)("apellido")
                Me.txt_nombre.Text = tabla.Rows(0)("nombre")
                Me.txt_numero_doc.Text = tabla.Rows(0)("numero_Doc")
                Me.txt_apellido.Focus()
                Exit Sub
            Else
                MsgBox("No se encontro ningun Empleado con ese documento", vbExclamation, "Importante")
                Me.txt_numero_doc.Text = ""
                Me.txt_apellido.Text = ""
                Me.txt_apellido.Focus()
            End If
        End If
    End Sub

    Private Sub buscar_por_apellido()
        Dim cons As String = ""
        acceso._tabla = "Empleado"

        Dim tabla As New Data.DataTable

        cons = "SELECT * FROM Empleado"
        cons &= " WHERE apellido LIKE '" & Me.txt_apellido.Text & "%'"

        tabla = acceso._consulta(cons)


        If tabla.Rows.Count <> 0 Then
            txt_apellido.Text = tabla.Rows(0)("apellido")
            Me.txt_nombre.Text = tabla.Rows(0)("nombre")
            Me.txt_numero_doc.Text = tabla.Rows(0)("numero_Doc")
            Me.txt_apellido.Focus()
            Exit Sub
        Else
            MsgBox("No se encontro ningun Empleado con ese apellido", vbExclamation, "Importante")
            Me.txt_numero_doc.Text = ""
            Me.txt_apellido.Text = ""
            Me.txt_apellido.Focus()
        End If
    End Sub

    'DESASIGNAR
    Private Sub desasignar()
        acceso._tabla = "EmpleadoxTurno"
        Dim sentencia As String

        'sentencia = "fecha_hasta= " & FormatDateTime(Now, DateFormat.ShortDate)
        sentencia = "UPDATE EmpleadoxTurno SET fecha_hasta = '" & FormatDateTime(Now, DateFormat.ShortDate) & "' WHERE NUMERO_DOC = " & txt_numero_doc.Text & " AND COD_TURNO = " & cmb_turno.SelectedValue & " and fecha_hasta is null"

        Try
            acceso._consulta(sentencia)
            'acceso._modificar(sentencia, "numero_Doc = " & grilla_turnos_empleado.CurrentRow.Cells("doc").Value & ", cod_Turno =" & grilla_turnos_empleado.CurrentRow.Cells("idturno").Value)
            MessageBox.Show("Exito al Desasignar Turno", "Éxito", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
        Catch ex As Exception
            MessageBox.Show("Error al Modificar en la base de datos" & vbCrLf + vbCrLf & "Mensaje de error: " & vbCrLf + ex.Message, "Error al Modificar en la base de datos", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)

        End Try
        limpiar()
    End Sub

    'ASIGNAR
    Private Sub asignar()
        acceso._tabla = "EmpleadoxTurno"
        Dim consulta As String

        consulta = "tipo_Doc=DNI"
        consulta += ", numero_Doc= " & txt_numero_doc.Text
        consulta += ", cod_Turno= " & cmb_turno.SelectedValue
        consulta += ", descripcion= " & txt_descipcion.Text
        consulta += ", fecha_desde= " & FormatDateTime(dtp_fecha_desde.Value, DateFormat.ShortDate)

        Try
            acceso._insertar(consulta)
            MessageBox.Show("Exito al Asignar Turno", "Éxito", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

        Catch ex As Exception
            MessageBox.Show("Error al Insertar en la base de datos" & vbCrLf + vbCrLf & "Mensaje de error: " & vbCrLf + ex.Message, "Error al Insertar en la base de datos", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)

        End Try

    End Sub

    Private Sub txt_descipcion_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_descipcion.TextChanged
        If busco = True Then
            btn_asignar.Enabled = True
        End If
    End Sub

    Private Sub frm_Asignacion_de_Turnos_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed
        Dispose()
    End Sub


    Private Sub txt_descipcion_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_descipcion.Leave
        If txt_descipcion.Text = "" And btn_salir.Focused = False Then
            lbl_descripcion_obligatorio.Visible = True
        Else
            lbl_descripcion_obligatorio.Visible = False
        End If
    End Sub

    Private Sub lbl_titulo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbl_titulo.Click

    End Sub
End Class